/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.TourOrderDetailDTO;
import east2west.interfaces.TourOrderDetailInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author thanhluan
 */
public class TourOrderDetailDAO implements TourOrderDetailInterface {

    Connection con = null;
    CallableStatement call = null;
    ResultSet rs = null;

    public ArrayList<TourOrderDetailDTO> readByAll() {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Call_View_TourOrderDetail}");
            ArrayList<TourOrderDetailDTO> list = new ArrayList<TourOrderDetailDTO>();
            rs = call.executeQuery();
            while (rs.next()) {

                TourOrderDetailDTO dto = new TourOrderDetailDTO();
                dto.setTourOrderDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setOrderId(rs.getInt(3));
                Date date = rs.getDate(4);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                dto.setTotalQty(rs.getInt(5));
                dto.setChildQty(rs.getInt(6));
                dto.setAmount(rs.getFloat(7));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;

        }
    }

    public ArrayList<TourOrderDetailDTO> findByStartDate(String startDate) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Call_View_TourOrderDetail_FindStartDate(?)}");
            call.setString(1, startDate);
            ArrayList<TourOrderDetailDTO> list = new ArrayList<TourOrderDetailDTO>();
            rs = call.executeQuery();
            while (rs.next()) {

                TourOrderDetailDTO dto = new TourOrderDetailDTO();
                dto.setTourOrderDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setOrderId(rs.getInt(3));
                Date date = rs.getDate(4);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                dto.setTotalQty(rs.getInt(5));
                dto.setChildQty(rs.getInt(6));
                dto.setAmount(rs.getFloat(7));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;

        }
    }

    public ArrayList<TourOrderDetailDTO> findByTotal(float total) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public TourOrderDetailDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Call_View_TourOrderDetail_FindId(?)}");
            call.setInt(1, id);
            TourOrderDetailDTO dto = null;
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new TourOrderDetailDTO();
                dto.setTourOrderDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setOrderId(rs.getInt(3));
                Date date = rs.getDate(4);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                dto.setTotalQty(rs.getInt(5));
                dto.setChildQty(rs.getInt(6));
                dto.setAmount(rs.getFloat(7));

            }
            return dto;
        } catch (SQLException ex) {
            return null;

        }
    }

    public ArrayList<TourOrderDetailDTO> findByTourOrderDetailDto(TourOrderDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean create(TourOrderDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_TourOrderDetail(?,?,?,?,?,?)}");

            call.setInt(1, dto.getTourId());
            call.setInt(2, dto.getOrderId());
            call.setString(3, dto.getStartDate());
            call.setInt(4, dto.getTotalQty());
            call.setInt(5, dto.getChildQty());
            call.setFloat(6, dto.getAmount());
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }

    }

    public boolean update(TourOrderDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Update_TourOrderDetail(?,?,?,?,?,?,?)}");
            call.setInt(1, dto.getTourOrderDetailId());
            call.setInt(2, dto.getTourId());
            call.setInt(3, dto.getOrderId());
            call.setDate(4, DateTimeUtil.convert2Date(dto.getStartDate(), "dd-MM-yyyy"));
            call.setInt(5, dto.getTotalQty());
            call.setInt(6, dto.getChildQty());
            call.setFloat(7, dto.getAmount());
            call.executeUpdate();
            con.commit();
            return true;
        } catch (SQLException ex) {
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                }
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }

    }

    public boolean delete(TourOrderDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Delete_TourOrderDetail(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            con.commit();
            return true;
        } catch (Exception ex) {
            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                }
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }

    }

    public ArrayList<TourOrderDetailDTO> findTourOrderDetailCancel(int tourOrderId) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_TourOrderDetailCancel where OrderId = ?");
            call.setInt(1, tourOrderId);
            ArrayList<TourOrderDetailDTO> list = new ArrayList<TourOrderDetailDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                TourOrderDetailDTO dto = new TourOrderDetailDTO();
                dto.setTourOrderDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setOrderId(rs.getInt(3));
                Date date = rs.getDate(4);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "MM/dd/yyyy"));
                }
                dto.setTotalQty(rs.getInt(5));
                dto.setChildQty(rs.getInt(6));
                dto.setAmount(rs.getFloat(7));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;

        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }
}
